#!/usr/bin/python
# -*- coding: UTF-8 -*- 
import MySQLdb
import sqlite3 
def readMysqlDB():
	
	# 打开数据库连接
	db = MySQLdb.connect("localhost","root","123456","tang_poetry" )
	
	# 使用cursor()方法获取操作游标 
	cursor = db.cursor()
	
	try:
		sql = "select id, title, (select name from poets where poetries.poet_id = poets.id), content from poetries;"
		cursor.execute(sql)
		results = cursor.fetchall()
		for row in results:
			idt = row[0]
			title = row[1]
			author = row[2]
			content = row[3]
			# print title, author, content
			dataList = [idt, title, author, content]
			insertDatas(dataList)

	except Exception as e:
		print e
		print "error: unable to fecth data"
	db.close()

def insertDatas(data):
	# print data
	conne = sqlite3.connect("/root/poetrise.sqlite")
	conne.text_factory = str
	curs = conne.cursor()
	sql = "create table if not exists poets (id int primary key, title text, author text, content text);"
	curs.execute(sql)
	insert = "insert into poets values (?, ?, ?, ?)"
	curs.execute(insert, data)
	conne.commit()
	conne.close()



readMysqlDB()
	
